package com.tledu.stu_manager.dao;

import cn.hutool.db.Db;
import cn.hutool.db.Entity;
import cn.hutool.db.Page;
import cn.hutool.db.PageResult;
import cn.hutool.db.sql.Condition;
import com.tledu.stu_manager.exception.CustomException;
import com.tledu.stu_manager.model.Student;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * @author Antg
 * @date 2021/7/3110:22
 */
public class StudentDao {

    /**
     * 根据用户名模糊查询
     *
     * @author Antg
     * @date 2021/7/31 23:31
     */
    public List<Student> fuzzyQueryStudent(String username) throws CustomException {
        try {
            List<Entity> queryList = Db.use().findBy("student_info", new Condition("del_state", "=", 0), new Condition("username", username, Condition.LikeType.Contains));
            List<Student> studentList = new ArrayList<>();
            //将实体进行转换
            for (Entity entity : queryList) {
                Student student = new Student();
                student.setId(entity.getInt("id"));
                student.setUsername(entity.getStr("username"));
                student.setPassword(entity.getStr("password"));
                student.setNickname(entity.getStr("nickname"));
                student.setAge(entity.getInt("age"));
                student.setBirthday(entity.getDate("birthday"));
                student.setCreateTime(entity.getDate("create_time"));
                student.setUpdateTime(entity.getDate("update_time"));
                studentList.add(student);
            }
            return studentList;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
            throw new CustomException("数据库连接异常");
        }
    }

    /**
     * 根据id查询学生
     *
     * @author Antg
     * @date 2021/7/31 19:16
     */
    public Student getStuById(Integer id) throws CustomException {
        try {
            List<Entity> queryList = Db.use().query("select * from student_info where id = ? and del_state = 0", id);
            if (queryList == null || queryList.size() == 0) {
                return null;
            } else {
                Student student = new Student();
                Entity entity = queryList.get(0);
                student.setId(entity.getInt("id"));
                student.setUsername(entity.getStr("username"));
                student.setPassword(entity.getStr("password"));
                student.setNickname(entity.getStr("nickname"));
                student.setAge(entity.getInt("age"));
                student.setBirthday(entity.getDate("birthday"));
                student.setCreateTime(entity.getDate("create_time"));
                student.setUpdateTime(entity.getDate("update_time"));
                return student;
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
            throw new CustomException("数据库连接异常");
        }
    }

    /**
     * 根据学生用户名查询用户
     *
     * @author Antg
     * @date 2021/7/31 10:26
     */
    public Student getStuByUsername(String username) throws CustomException {
        try {
            List<Entity> queryList = Db.use().query("select * from student_info where username = ? and del_state = 0", username);
            if (queryList == null || queryList.size() == 0) {
                return null;
            } else {
                Student student = new Student();
                Entity entity = queryList.get(0);
                student.setId(entity.getInt("id"));
                student.setUsername(entity.getStr("username"));
                student.setPassword(entity.getStr("password"));
                student.setNickname(entity.getStr("nickname"));
                student.setAge(entity.getInt("age"));
                student.setBirthday(entity.getDate("birthday"));
                student.setCreateTime(entity.getDate("create_time"));
                student.setUpdateTime(entity.getDate("update_time"));
                return student;
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
            throw new CustomException("数据库连接异常");
        }

    }

    /**
     * 查询所有学生
     *
     * @author Antg
     * @date 2021/7/31 13:46
     */
    public List<Student> findAll() throws CustomException {
        String sql = "select * from student_info where del_state = 0";
        List<Student> studentList = new ArrayList<>();
        try {
            List<Entity> queryList = Db.use().query(sql);
            //将实体进行转换
            for (Entity entity : queryList) {
                Student student = new Student();
                student.setId(entity.getInt("id"));
                student.setUsername(entity.getStr("username"));
                student.setPassword(entity.getStr("password"));
                student.setNickname(entity.getStr("nickname"));
                student.setAge(entity.getInt("age"));
                student.setBirthday(entity.getDate("birthday"));
                student.setCreateTime(entity.getDate("create_time"));
                student.setUpdateTime(entity.getDate("update_time"));
                studentList.add(student);
            }
            return studentList;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
            throw new CustomException("数据库连接异常");
        }
    }

    /**
     * 查询该表的字段总数
     *
     * @author Antg
     * @date 2021/8/2 11:17
     */
    public Integer getNum() throws CustomException {
        String sql = "select count(1) as num from student_info where del_state = 0";
        try {
            List<Entity> query = Db.use().query(sql);
            Integer num = query.get(0).getInt("num");
            return num;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
            throw new CustomException("数据库连接异常");
        }
    }

    /**
     * 分页查询
     *
     * @author Antg
     * @date 2021/8/2 11:21
     */

    public List<Student> findWithPage(Integer pageNum, Integer pageSize) throws CustomException {
        try {
            PageResult<Entity> list = Db.use().page(Entity.create("student_info").set("del_state", 0), new Page(pageNum - 1, pageSize));
            List<Student> studentList = new ArrayList<>();
            //将实体进行转换
            for (Entity entity : list) {
                Student student = new Student();
                student.setId(entity.getInt("id"));
                student.setUsername(entity.getStr("username"));
                student.setPassword(entity.getStr("password"));
                student.setNickname(entity.getStr("nickname"));
                student.setAge(entity.getInt("age"));
                student.setBirthday(entity.getDate("birthday"));
                student.setCreateTime(entity.getDate("create_time"));
                student.setUpdateTime(entity.getDate("update_time"));
                studentList.add(student);
            }
            return studentList;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
            throw new CustomException("数据库连接异常");
        }
    }


    /**
     * 添加学生
     *
     * @author Antg
     * @date 2021/7/31 14:35
     */
    public void addStudent(Student student) throws CustomException {
        String sql = "insert into student_info(username, password, nickname, age, birthday)  value (?,?,?,?,?)";
        try {
            Db.use().execute(sql, student.getUsername(), student.getPassword(), student.getNickname(), student.getAge(), student.getBirthday());

        } catch (SQLException throwables) {
            throwables.printStackTrace();
            throw new CustomException("数据库连接异常");
        }
    }

    /**
     * 更新学生信息
     *
     * @author Antg
     * @date 2021/7/31 14:58
     */
    public void modifyStudent(Student student) throws CustomException {
        String sql = "update student_info set password = ?,nickname = ?,age = ?,birthday = ? where id = ?";
        try {
            Db.use().execute(sql, student.getPassword(), student.getNickname(), student.getAge(), student.getBirthday(), student.getId());
        } catch (SQLException throwables) {
            throwables.printStackTrace();
            throw new CustomException("数据库连接异常");
        }
    }

    /**
     * 删除学生(将删除字段更新为1)
     *
     * @author Antg
     * @date 2021/7/31 18:27
     */
    public void delStudent(Integer id) throws CustomException {
        String sql = "update student_info set del_state = 1 where id = ?";
        try {
            Db.use().execute(sql, id);

        } catch (SQLException throwables) {
            throwables.printStackTrace();
            throw new CustomException("数据库连接异常");
        }
    }

    /**
     * 批量删除
     *
     * @author Antg
     * @date 2021/7/31 20:04
     */
    public void batchDelStudent(Integer[] idList) throws CustomException {
        String paramStr = "";
        for (int i = 0; i < idList.length; i++) {
            if (i == idList.length - 1) {
                paramStr += "?";
                break;
            }
            paramStr += "?,";
        }
        String sql = "update student_info set del_state = 0 where id in (" + paramStr + ")";
        try {
            Db.use().execute(sql, idList);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
            throw new CustomException("数据库连接异常");
        }

    }


}
